home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI784.ASC
< prev
next >
Wrap
Text File
|
1992-08-12
|
6KB
|
199 lines
PRODUCT : Paradox NUMBER : 784
VERSION : 3.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 1/3
TITLE : Doing "Not-In" Queries in Paradox
This query is made to ask the question "What records exist in one
table that do NOT exist in another?" The following is a general
example method for creating "Not-In" queries.
Notice that this example uses the COUNT operation and it does not
use NOT or NO. For the examples, an underscore character will be
used in place of the [F5] key to indicate an example element in
the query.
First create tables with the structures shown below.
CUSTOMER╦════Cust #═════╦════Last Name═════╦════First Name════╗
1 ║ 1015 ║ Elkins ║ Eugene ║
2 ║ 1020 ║ Fenton ║ Mark ║
3 ║ 1055 ║ Harding ║ Jim ║
4 ║ 1056 ║ Wilden ║ Marc ║
5 ║ 1319 ║ Fenton ║ Teresa ║
6 ║ 1341 ║ Goves ║ Cynthia ║
7 ║ 1342 ║ Goves ║ Joseph ║
8 ║ 1685 ║ Moon ║ Yvonne ║
9 ║ 1988 ║ Martinez ║ Susan ║
10 ║ 2022 ║ Staebell ║ Alice ║
ORDERS═╦════Order #════╦═════Date═════╦════Cust #═════╦Part #╦
1 ║ 1 ║ 7/09/89 ║ 1015 ║ DR ║
2 ║ 2 ║ 7/09/89 ║ 1341 ║ WR ║
3 ║ 3 ║ 7/09/89 ║ 1685 ║ HA ║
4 ║ 4 ║ 7/28/89 ║ 1988 ║ HA ║
5 ║ 5 ║ 8/01/89 ║ 2022 ║ WR ║
6 ║ 6 ║ 8/03/89 ║ 1988 ║ GH ║
7 ║ 7 ║ 8/04/89 ║ 1341 ║ DR ║
8 ║ 8 ║ 8/14/89 ║ 1055 ║ VI ║
9 ║ 9 ║ 8/15/89 ║ 1020 ║ DR ║
10 ║ 10 ║ 8/16/89 ║ 1341 ║ GH ║
11 ║ 11 ║ 8/18/89 ║ 1015 ║ TB ║
12 ║ 12 ║ 8/21/89 ║ 1685 ║ TB ║
(The next table is a continuation of the above orders table)
ORDERS═╦══════Price═══════╦
1 ║ 46.20 ║
PRODUCT : Paradox NUMBER : 784
VERSION : 3.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 2/3
TITLE : Doing "Not-In" Queries in Paradox
2 ║ 3.96 ║
3 ║ 12.04 ║
4 ║ 12.04 ║
5 ║ 3.96 ║
6 ║ 14.52 ║
7 ║ 46.20 ║
8 ║ 65.93 ║
9 ║ 46.20 ║
10 ║ 14.52 ║
11 ║ 33.66 ║
12 ║ 33.66 ║
The query shown below is how to ask the question "Which customers
have never placed an order?". This query works for the following
reasons. First, it uses the 'inclusion operator' which is the
exclamation point <!> in the [Cust #] field of the CUSTOMER
table. This tells Paradox to consider every customer number,
whether or not it is found in the ORDERS table.
Next, the ORDERS table contains the expression "count=0" in the
[Cust #] field. This tells Paradox to count the number of
occurrences of each customer number in the ORDERS tables, and
only include those which occur zero times. In other words, show
the customers who have not placed any orders, or show everything
in the CUSTOMER table that is NOT in the ORDERS table.
ORDERS═╦Order #═╦══Date═══╦════Cust #════╦═Part #══╦══Price═══╗
║ ║ ║ _c,count=0 ║ ║ ║
║ ║ ║ ║ ║ ║
║ ║ ║ ║ ║ ║
CUSTOMER╦═Cust #═╦═Last Name══╦═First Name═╗
║√ _c! ║ ║ ║
║ ║ ║ ║
║ ║ ║ ║
ANSWER═╦══Cust #═══╗
1 ║ 1056 ║
2 ║ 1319 ║
3 ║ 1342 ║
PRODUCT : Paradox NUMBER : 784
VERSION : 3.0 & up
OS : DOS
DATE : August 12, 1992 PAGE : 3/3
TITLE : Doing "Not-In" Queries in Paradox
To help understand how this query works, try changing the
"count=0" to a "calc count all". The ANSWER that will be
returned will have 10 records, since there are 10 records in the
CUSTOMER table. The number shown will be the number of times
each customer appears in the ORDERS table. And you will see that
the records 1056, 1319 and 1342 show a count of zero, which are
the answer to the above query.
The information in this document came from Brian J. Smith's
article "How To Ask 'Not-In' Queries" which first appeared in the
October 1989 issue of "Instant Scripts", a publication of the
LAPALS user group in Southern California.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.